package com.iyundao.io.mapper;

import com.iyundao.io.model.PartyMember;
import com.iyundao.io.model.Ratio;
import com.iyundao.io.model.StatisticsModel;
import com.iyundao.io.model.Sum;
import com.iyundao.io.tools.StaticsShow;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Repository
public interface StatisticsMapper {

    @Select("SELECT count(*) numberofactivity,DATE_FORMAT(activitycreatetime,'%Y-%m') as month," +
            "sum(activitynumber) as joinnumber, sum(activityjoinnumber) AS realitynumber, " +
            "sum(activityjoinnumber)/sum(activitynumber) probability " +
            "FROM activitymodel " +
            "where organizationid in " +
            "(select sid from sys_dept " +
            "where sid = #{organizationid,jdbcType=VARCHAR}" +
            " or parent_id = #{organizationid,jdbcType=VARCHAR}) " +
            " GROUP BY DATE_FORMAT(activitycreatetime,'%Y-%m')")
    @Results({
            @Result(column="numberofactivity", property="numberofactivity", jdbcType= JdbcType.INTEGER),
            @Result(column="month", property="month", jdbcType=JdbcType.VARCHAR),
            @Result(column="joinnumber", property="joinnumber", jdbcType=JdbcType.INTEGER),
            @Result(column="realitynumber", property="realitynumber", jdbcType=JdbcType.INTEGER),
            @Result(column="probability", property="probability", jdbcType=JdbcType.DOUBLE)
    })
    List<StatisticsModel> selectGroupByOrganizationId(String organizationid);

    @Select(" SELECT id, activitytitle,activitynumber as joinnumber,activityjoinnumber as realitynumber," +
            "activityjoinnumber/activitynumber probability FROM activitymodel " +
            "WHERE organizationid = #{organizationid,jdbcType=VARCHAR} and" +
            " DATE_FORMAT( activitycreatetime, '%Y%m' ) =  DATE_FORMAT(#{date,jdbcType=TIMESTAMP}, '%Y%m' )")
    List<StatisticsModel> selectByMonth(Date date, String organizationid);

    @Select("select * from partymember where " +
            "id in (SELECT partymemberid from activityjoinpeople where activityid = #{id,jdbcType=INTEGER}) " +
            "and " +
            "id not in (select partymemberid from signin where activityid = #{id,jdbcType=INTEGER})")
    List<PartyMember> selectNotJoin(Integer id);

    /**
     * 组织下活动排名
     * @param organizationid
     * @param date1
     * @param date2
     * @return
     */
    @Select("select organizationid from activitymodel " +
            "where organizationid in (select sid from sys_dept where parent_id = #{organizationid,jdbcType=VARCHAR}) " +
            "AND activitycreatetime >= #{date1,jdbcType=VARCHAR} AND activitycreatetime <= #{date2,jdbcType=VARCHAR} " +
            "GROUP BY organizationid order by COUNT(organizationid) desc")
    List<Integer> selectActivityNumber(String organizationid,String date1, String date2);

    /**
     * 组织下参与率排名
     * @param organizationid
     * @param date1
     * @param date2
     * @return
     */
    @Select("select organizationid from activitymodel " +
            "where organizationid in (select sid from sys_dept where parent_id = #{organizationid,jdbcType=VARCHAR}) " +
            "AND activitycreatetime >= #{date1,jdbcType=VARCHAR} AND activitycreatetime <= #{date2,jdbcType=VARCHAR} " +
            "GROUP BY organizationid ORDER BY SUM(activityjoinnumber)/SUM(activitynumber) DESC")
    List<Integer> slectByRatio(String organizationid,String date1, String date2);


    @Select("SELECT COUNT(aj.partymemberid) num,p.partymembername,p.id from partymember p " +
            "left JOIN activityjoinpeople aj " +
            "ON p.id=aj.partymemberid " +
            "left JOIN partymemberorganization po " +
            "on po.partymemberid = p.id " +
            "LEFT JOIN (SELECT * from activitymodel where activitystarttime >= #{date1,jdbcType=VARCHAR} " +
            "and activitystarttime <= #{date2,jdbcType=VARCHAR}) a " +
            "on aj.activityid=a.id and a.organizationid=po.organizationid " +
            "where po.organizationid in (select sid from sys_dept where sid=#{organizationid,jdbcType=VARCHAR} or parent_id=#{organizationid,jdbcType=VARCHAR})  " +
            "AND ISNULL(po.outtime) " +
            "GROUP BY p.id " +
            "ORDER BY p.id DESC")
    List<StaticsShow> orderByNumber(String organizationid, String date1, String date2);

    @Select("SELECT count(DISTINCT(s.activityid))/COUNT(DISTINCT(aj.activityid)) ratio, count(DISTINCT(s.activityid)),COUNT(DISTINCT(aj.activityid)), p.partymembername,p.id from partymember p " +
            "left JOIN signin s " +
            "on p.id=s.partymemberid " +
            "left JOIN partymemberorganization po " +
            "on po.partymemberid = p.id " +
            "left JOIN activityjoinpeople aj " +
            "on  p.id=aj.partymemberid " +
            "LEFT JOIN (SELECT * from activitymodel where activitystarttime >= #{date1,jdbcType=VARCHAR} " +
            "and activitystarttime <= #{date2,jdbcType=VARCHAR}) a " +
            "on s.activityid=a.id and a.organizationid=po.organizationid " +
            "WHERE po.organizationid in (select sid from sys_dept where sid=#{organizationid,jdbcType=VARCHAR} or parent_id=#{organizationid,jdbcType=VARCHAR}) " +
            "AND ISNULL(po.outtime) " +
            "GROUP BY p.id " +
            "ORDER BY p.id DESC")
    List<Ratio> orderbyRatio(String organizationid,String date1, String date2);

    @Select("SELECT SUM(i.integralrecordnum) sum,p.partymembername,p.id from partymember p " +
            "LEFT JOIN integralrecord i " +
            "on i.partymemberid=p.id " +
            "LEFT JOIN partymemberorganization po " +
            "on po.partymemberid=p.id " +
            "LEFT JOIN (SELECT * from activitymodel where activitystarttime >= #{date1,jdbcType=VARCHAR} " +
            "and activitystarttime <= #{date2,jdbcType=VARCHAR}) a " +
            "on i.activityid=a.id and a.organizationid=po.organizationid " +
            "where po.organizationid in (SELECT sid from sys_dept where sid=#{organizationid,jdbcType=VARCHAR} or parent_id=#{organizationid,jdbcType=VARCHAR}) " +
            "AND ISNULL(po.outtime) " +
            "GROUP BY p.id " +
            "ORDER BY p.id DESC ")
    List<Sum> orderBySum(String organizationid,String date1, String date2);
}
